package com.lab.idea.bl.service.impl;

import cn.hutool.core.codec.Base64Encoder;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.thread.GlobalThreadPool;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.digest.DigestUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.GlobalDbConfig;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.system.SystemUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.lab.idea.bl.entity.*;
import com.lab.idea.bl.mapper.ExcelTemplateMapper;
import com.lab.idea.bl.pool.LabThreadPool;
import com.lab.idea.bl.pool.LabThreadPoolHelper;
import com.lab.idea.bl.service.*;
import com.lab.idea.bl.util.BlExcelReadUtil;
import com.lab.idea.bl.vo.QueryData;
import com.lab.idea.common.exception.LabException;
import com.lab.idea.common.query.LabQuery;
import com.lab.idea.common.query.QuerySort;
import com.lab.idea.common.util.LabJsonUtil;
import com.lab.idea.mapper.schema.JdbcColumn;
import com.lab.idea.mapper.schema.JdbcSchema;
import com.lab.idea.mapper.schema.JdbcTable;
import com.lab.idea.module.method.ScriptHelper;
import com.lab.idea.module.report.ReportHelper;
import com.lab.idea.module.report.ReportResponse;
import com.lab.idea.module.report.ReportTemplate;
import com.lab.idea.mybatis.service.impl.LabServiceImpl;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.Serializable;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * <p>
 * excel模板 服务实现类
 * </p>
 *
 * @author lab
 * @since 2023-11-06
 */
@Slf4j
@Service
public class ExcelTemplateServiceImpl extends LabServiceImpl<ExcelTemplateMapper, ExcelTemplate> implements ExcelTemplateService {

    @Autowired
    private ExcelOutService excelOutService;

    @Autowired
    private ExcelComputeService excelComputeService;

    @Autowired
    private ExcelSheetService excelSheetService;

    @Autowired
    private ExcelColumnService excelColumnService;

    @Autowired
    private ExcelJoinService excelJoinService;

    @Autowired
    private ExcelQueryService excelQueryService;

    @Autowired
    private ExcelDocxService excelDocxService;

    @Autowired
    private ExcelFilterService excelFilterService;

    @Override
    protected boolean saveCheck(ExcelTemplate entity) {
        if (StrUtil.isBlankIfStr(entity.getExcelName())) {
            throw new LabException("模板名称为空");
        }
        QueryWrapper<ExcelTemplate> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("excelName",entity.getExcelName());
        List<ExcelTemplate> list = list(queryWrapper);
        if (!CollUtil.isEmpty(list)) {
            for (ExcelTemplate excelTemplate : list) {
                if (!StrUtil.equals(excelTemplate.getExcelId(),entity.getExcelId())) {
                    throw new LabException(String.format("模板名称 %s 已存在",entity.getExcelName()));
                }
            }
        }
        return true;
    }

    @Override
    @Transactional(rollbackFor = LabException.class)
    public boolean saveOrUpdate(ExcelTemplate entity) {
        if (super.saveOrUpdate(entity)) {
            if (!StrUtil.isBlankIfStr(entity.getCopyId()) && !StrUtil.equals(entity.getExcelId(),entity.getCopyId())) {
                // 进入复制模式
                LabQuery labQuery = new LabQuery().setPageSize(-1);
                labQuery.eq("excelId",entity.getCopyId());
                List<ExcelSheet> excelSheets = excelSheetService.query(labQuery);
                if (CollUtil.isEmpty(excelSheets) ) {
                    return true;
                }
                Map<String,ExcelSheet> excelSheetMap = new HashMap<>();
                for (ExcelSheet excelSheet : excelSheets) {
                    excelSheet.setCopyId(excelSheet.getSheetId()).setSheetId(IdWorker.getIdStr())
                            .setExcelId(entity.getExcelId());
                    excelSheetMap.put(excelSheet.getCopyId(),excelSheet);
                }
                List<ExcelColumn> excelColumns = excelColumnService.query(labQuery);
                if (CollUtil.isEmpty(excelColumns)) {
                    return true;
                }
                List<ExcelColumn> insertColumns = new ArrayList<>();
                for (ExcelColumn excelColumn : excelColumns) {
                    if (excelSheetMap.containsKey(excelColumn.getSheetId())) {
                        ExcelSheet excelSheet = excelSheetMap.get(excelColumn.getSheetId());
                        excelColumn.setCopyId(excelColumn.getColumnId()).setColumnId(IdWorker.getIdStr())
                                .setExcelId(entity.getExcelId()).setSheetId(excelSheet.getSheetId());
                        insertColumns.add(excelColumn);
                    }
                }
                excelSheetService.saveBatch(excelSheetMap.values());
                excelColumnService.saveBatch(insertColumns,500);

                List<ExcelOut> excelOuts = excelOutService.query(labQuery);
                if (CollUtil.isEmpty(excelOuts)) {
                    return true;
                }
                Map<String,ExcelOut> excelOutMap = new HashMap<>();
                for (ExcelOut excelOut : excelOuts) {
                    excelOut.setExcelId(entity.getExcelId()).setCopyId(excelOut.getOutId()).setOutId(IdWorker.getIdStr());
                    excelOutMap.put(excelOut.getCopyId(),excelOut);
                }
                excelOutService.saveBatch(excelOutMap.values());
                // 复制查询
                List<ExcelQuery> excelQueries = excelQueryService.query(labQuery);
                if (!CollUtil.isEmpty(excelQueries)) {
                    List<ExcelQuery> inserts = new ArrayList<>();
                    for (ExcelQuery excelQuery : excelQueries) {
                        if (excelOutMap.containsKey(excelQuery.getOutId())) {
                            ExcelOut excelOut = excelOutMap.get(excelQuery.getOutId());
                            excelQuery.setExcelId(entity.getExcelId()).setOutId(excelOut.getOutId()).setQueryId(IdWorker.getIdStr());
                            inserts.add(excelQuery);
                        }
                    }
                    excelQueryService.saveBatch(inserts,500);
                }
                // 过滤配置
                List<ExcelFilter> excelFilters = excelFilterService.query(labQuery);
                if (!CollUtil.isEmpty(excelFilters)) {
                    List<ExcelFilter> inserts = new ArrayList<>();
                    for (ExcelFilter excelFilter : excelFilters) {
                        if (excelOutMap.containsKey(excelFilter.getOutId())) {
                            ExcelOut excelOut = excelOutMap.get(excelFilter.getOutId());
                            excelFilter.setExcelId(entity.getExcelId()).setOutId(excelOut.getOutId()).setFilterId(IdWorker.getIdStr());
                            inserts.add(excelFilter);
                        }
                    }
                    excelFilterService.saveBatch(inserts,500);
                }
                // 关联配置
                List<ExcelJoin> excelJoins = excelJoinService.query(labQuery);
                if (!CollUtil.isEmpty(excelJoins)) {
                    List<ExcelJoin> inserts = new ArrayList<>();
                    for (ExcelJoin excelJoin : excelJoins) {
                        if (excelOutMap.containsKey(excelJoin.getOutId())) {
                            ExcelOut excelOut = excelOutMap.get(excelJoin.getOutId());
                            excelJoin.setExcelId(entity.getExcelId()).setOutId(excelOut.getOutId()).setJoinId(IdWorker.getIdStr());
                            inserts.add(excelJoin);
                        }
                    }
                    excelJoinService.saveBatch(inserts,500);
                }
                // 计算配置
                List<ExcelCompute> computeList = excelComputeService.query(labQuery);
                if (!CollUtil.isEmpty(computeList)) {
                    List<ExcelCompute> inserts = new ArrayList<>();
                    for (ExcelCompute excelCompute : computeList) {
                        if (excelOutMap.containsKey(excelCompute.getOutId())) {
                            ExcelOut excelOut = excelOutMap.get(excelCompute.getOutId());
                            excelCompute.setExcelId(entity.getExcelId()).setOutId(excelOut.getOutId()).setComputeId(IdWorker.getIdStr());
                            inserts.add(excelCompute);
                        }
                    }
                    excelComputeService.saveBatch(inserts,500);
                }
                // 报告配置
                List<ExcelDocx> docxList = excelDocxService.query(labQuery);
                if (!CollUtil.isEmpty(docxList)) {
                    List<ExcelDocx> inserts = new ArrayList<>();
                    for (ExcelDocx excelDocx : docxList) {
                        if (excelOutMap.containsKey(excelDocx.getOutId())) {
                            ExcelOut excelOut = excelOutMap.get(excelDocx.getOutId());
                            excelDocx.setExcelId(entity.getExcelId()).setOutId(excelOut.getOutId()).setDocxId(IdWorker.getIdStr());
                            inserts.add(excelDocx);
                        }
                    }
                    excelDocxService.saveBatch(inserts,500);
                }
            }
            return true;
        }
        return false;
    }

    @Override
    @Transactional(rollbackFor = LabException.class)
    public boolean removeById(Serializable id) {
        ExcelTemplate excelTemplate = getById(id);
        if (excelTemplate == null) {
            return true;
        }
        LabQuery labQuery = new LabQuery().setPageSize(-1);
        labQuery.eq("excelId", excelTemplate.getExcelId());
        excelSheetService.remove(labQuery);
        excelColumnService.remove(labQuery);
        excelOutService.remove(labQuery);
        excelQueryService.remove(labQuery);
        excelJoinService.remove(labQuery);
        excelComputeService.remove(labQuery);
        excelDocxService.remove(labQuery);
        excelFilterService.remove(labQuery);
        return excelTemplate.deleteById();
    }

    @Override
    @Transactional(rollbackFor = LabException.class)
    public void init(String excelId,List<String> sheetIds) {
        if (StrUtil.isBlankIfStr(excelId)) {
            return;
        }
        ExcelTemplate excelTemplate = getById(excelId);
        if (excelTemplate == null) {
            throw new LabException("模板不存在");
        }
        if (StrUtil.isBlankIfStr(excelTemplate.getExcelPath())) {
            throw new LabException("文件路径为空");
        }
        if (!FileUtil.exist(excelTemplate.getExcelPath())) {
            throw new LabException("路径下缺少模板文件");
        }
        LabQuery labQuery = new LabQuery().setPageSize(-1);
        labQuery.eq("excelId",excelId);
        List<ExcelSheet> sheets = excelSheetService.query(labQuery);
        if (!CollUtil.isEmpty(sheets)) {
            excelTemplate.setSheets(sheets);
            excelColumnService.remove(labQuery);
        }
        List<ExcelSheet> excelSheets = BlExcelReadUtil.readSheet(excelTemplate);
        if (!CollUtil.isEmpty(excelSheets)) {
            List<ExcelColumn> columns = new ArrayList<>();
            List<ExcelSheet> removes = new ArrayList<>();
            for (ExcelSheet excelSheet : excelSheets) {
                columns.addAll(excelSheet.getColumns());
                /*if (!CollUtil.isEmpty(sheetIds) && !sheetIds.contains(excelSheet.getSheetId())) {
                    removes.add(excelSheet);
                } else {
                    columns.addAll(excelSheet.getColumns());
                }*/
            }
            if (!CollUtil.isEmpty(removes)) {
                excelSheets.removeAll(removes);
            }
            if (!CollUtil.isEmpty(excelSheets)) {
                excelSheetService.saveOrUpdateBatch(excelSheets,excelSheets.size());
//            excelColumnService.saveBatch(columns,columns.size());
                excelColumnService.saveBatch(columns);
            }
        }
    }

    @Override
    public QueryData queryData(String outId, JSONObject args) {
        ExcelOut excelOut = excelOutService.getById(outId);
        if (excelOut == null) {
            throw new LabException("输出不存在");
        }
        return queryData(excelOut,args);
    }

    /**
     * 去lims查询数据
     * @param excelOut  输出
     * @param args      参数
     * @return          查询结果
     */
    private QueryData queryLims(ExcelTemplate excelTemplate,ExcelOut excelOut,JSONObject args){
        // 查询返回列
        QueryData queryData = new QueryData().setDatas(CollUtil.newArrayList());
        LabQuery labQuery = new LabQuery().setPageSize(-1).setSorts(CollUtil.newArrayList(new QuerySort().setField("columnOrder").setAsc(true)));
        labQuery.eq("excelId",excelOut.getExcelId()).eq("sheetName",excelOut.getOutName());
        queryData.setColumns(excelColumnService.query(labQuery));
        if (CollUtil.isEmpty(queryData.getColumns())) {
            return queryData;
        }
        // 查询辅助SQL
        labQuery = new LabQuery().setPageSize(-1);
        labQuery.eq("outId",excelOut.getOutId());
        List<ExcelQuery> queries = excelQueryService.query(labQuery);
        if (queries == null) {
            queries = new ArrayList<>();
        }
        // 主查询和辅助查询同时执行
        queries.add(new ExcelQuery().setQueryId(excelOut.getOutId()).setQuerySql(excelOut.getQuerySql()).setQueryName(excelOut.getOutName()));
        // 切换到目标数据库
        labDynamicSourceService.pushDataSource("lims");
        try {
            TimeInterval timer = DateUtil.timer();
            GlobalDbConfig.setCaseInsensitive(false);
            Db limsDb = Db.use(labDynamicSourceService.getDataSource());
//            String startNo = args.getStr("startNo",StrUtil.EMPTY);
//            String endNo = args.getStr("endNo",StrUtil.EMPTY);
            String typeStr = args.getStr("typeStr",StrUtil.EMPTY);
            if (!StrUtil.isBlankIfStr(excelOut.getBeforeSql())
//                    && !StrUtil.isBlankIfStr(startNo) && !StrUtil.isBlankIfStr(endNo)
                    && CollUtil.newArrayList("内部平行样","明码平行样","内部质控样","明码质控样","复测样").contains(typeStr)) {
                log.info("---> {} 开始前置查询",excelOut.getOutName());
                String formatSql = formatSql(excelOut.getBeforeSql(), args);
                List<JSONObject> list = queryDataSource(limsDb, formatSql, args);
                log.info("---> {} 前置查询SQL\n {}",excelOut.getOutName(),formatSql);
                log.info("---> {} 前置查询行数和耗时 {} -> {}",excelOut.getOutName(),timer.intervalRestart(), list.size());
                if (!StrUtil.isBlankIfStr(excelOut.getBeforeScript())) {
                    args.set("beforeResult", list);
                    // 前置处理，增加环境参数
                    Object expression = ScriptHelper.expressionException(excelOut.getBeforeScript(), args, DigestUtil.md5Hex(excelOut.getBeforeScript()));
                    if (expression instanceof Map) {
                        JSONObject parsedObj = JSONUtil.parseObj(expression);
                        args.putAll(parsedObj);
                    }
                }
                log.info("---> {} 前置处理耗时:{}", excelOut.getOutName(),timer.intervalRestart());
            }
            // 开始主查询
//            queryData.setDatas(queryDataSource(limsDb,excelOut.getQuerySql(),args));
            // key: 查询ID.value:结果
            Map<String,List<JSONObject>> queryMap = new ConcurrentHashMap<>();
            if (!CollUtil.isEmpty(queries)) {
                for (ExcelQuery query : queries) {
                    query.setQuerySql(formatSql(query.getQuerySql(),args));
                }
                // 开始辅助SQL查询
                LabThreadPool threadPool = excelTemplate.threadPool();
                Semaphore semaphore = new Semaphore(NumberUtil.min(threadPool.getMaxPoolSize(),queries.size()),true);
                CountDownLatch downLatch = new CountDownLatch(queries.size());
                for (ExcelQuery excelQuery : queries) {
                    semaphore.acquire();
                    LabThreadPoolHelper.addTask(threadPool, new Runnable() {
                        @Override
                        public void run() {
                            TimeInterval queryTimer = DateUtil.timer();
                            log.info("\n---> {} {} 开始",excelOut.getOutName(),excelQuery.getQueryName());
                            try {
                                List<JSONObject> list = queryDataSource(limsDb, excelQuery.getQuerySql(), args);
                                if (!CollUtil.isEmpty(list)) {
                                    queryMap.put(excelQuery.getQueryId(),list);
                                }
                            } catch (Exception e) {
                                log.error("\n---> {} {} 异常:{}",excelOut.getOutName(),excelQuery.getQueryName(),e.getMessage());
                            }finally {
                                long intervaled = queryTimer.intervalRestart();
                                if (intervaled > 3000) {
                                    log.info("\n---> {} {} SQL:\n{}",excelOut.getOutName(),excelQuery.getQueryName(),excelQuery.getQuerySql());
                                    log.info("\n---> {} {} 耗时:{}",excelOut.getOutName(),excelQuery.getQueryName(),intervaled);
                                } else {
                                    log.info("\n---> {} {} 完成",excelOut.getOutName(),excelQuery.getQueryName());
                                }
                                semaphore.release();
                                downLatch.countDown();
                            }
                        }
                    });
                }
                boolean await = downLatch.await(120, TimeUnit.SECONDS);
                log.info("---> {} 查询执行情况{}",excelOut.getOutName(),await);
//                LabThreadPoolHelper.printState(threadPool);
                queryMapper(queryData,queryMap,queries,excelOut);
                log.info("---> {} 统计查询总耗时:{}", excelOut.getOutName(),timer.intervalRestart());
            }
            log.info("---> {} 开始查询数据处理...",excelOut.getOutName());
            // 查询后公式数据处理
            if (!StrUtil.isBlankIfStr(excelOut.getQueryScript())) {
                JSONObject env = new JSONObject();
                env.set("columns",queryData.getColumns());
                env.set("datas",queryData.getDatas());
                if (!CollUtil.isEmpty(queries)) {
                    // 加入辅助查询结果在 环境参数env 中
                    for (ExcelQuery query : queries) {
                        if (StrUtil.isBlankIfStr(query.getJoinProp())) {
                            continue;
                        }
                        if (queryMap.containsKey(query.getQueryId())) {
                            env.set(query.getJoinProp(),queryMap.get(query.getQueryId()));
                        }
                    }
                }
                // 返回结果为最终查询结果
                Object expression = ScriptHelper.expressionException(excelOut.getQueryScript(), env, DigestUtil.md5Hex(excelOut.getQueryScript()));
                if (expression instanceof Collection) {
                    queryData.setDatas((List<JSONObject>) expression);
                }
            }
            // 移除添加的辅助查询结果引用
            if (!CollUtil.isEmpty(queries) && !CollUtil.isEmpty(queryData.getDatas())) {
                for (JSONObject data : queryData.getDatas()) {
                    for (ExcelQuery query : queries) {
                        data.remove(query.getJoinProp());
                        data.remove(query.getQueryId());
                    }
                }
            }
            log.info("---> {} 数据处理耗时:{}", excelOut.getOutName(),timer.intervalRestart());
        }catch (Exception e){
            log.error("数据查询失败",e);
            throw new LabException("查询失败");
        } finally {
            labDynamicSourceService.clearDataSource();
        }
        return queryData;
    }

    /**
     * 辅助查询补充
     * @param queryData     查询结果
     * @param queryMap      查询结果
     * @param queries       辅助查询
     */
    private void queryMapper(QueryData queryData,Map<String,List<JSONObject>> queryMap,List<ExcelQuery> queries,ExcelOut excelOut) {
        // key: 查询Id, key: 匹配值，value: 匹配下 结果集合
        Map<String,Map<String,List<JSONObject>>> queryDataMap = new HashMap<>();
        for (ExcelQuery query : queries) {
            if (!queryMap.containsKey(query.getQueryId())) {
                continue;
            }
            if (StrUtil.equals(query.getQueryId(),excelOut.getOutId())) {
                // 主查询结果
                queryData.setDatas(queryMap.get(query.getQueryId()));
            } else if (!StrUtil.isBlankIfStr(query.getMatchColumn())) {
                // 匹配辅助查询结果
                String format = query.toFormat(query.getMatchColumn());
                // 查询结果
                List<JSONObject> list = queryMap.get(query.getQueryId());
                // key: 匹配值，value: 匹配下 结果集合
                Map<String,List<JSONObject>> listMap = new HashMap<>();
                for (JSONObject object : list) {
                    // 格式化出 匹配值
                    String keyVal = LabJsonUtil.formatCode(format, object);
                    if (StrUtil.isBlankIfStr(keyVal)) {
                        continue;
                    }
                    if (listMap.containsKey(keyVal)) {
                        listMap.get(keyVal).add(object);
                    } else {
                        List<JSONObject> val = new ArrayList<>();
                        val.add(object);
                        listMap.put(keyVal,val);
                    }
                }
                queryDataMap.put(query.getQueryId(),listMap);
            }
        }
        if (!CollUtil.isEmpty(queryDataMap) && !CollUtil.isEmpty(queryData.getDatas())) {
            // 循环主查询匹配
            for (JSONObject data : queryData.getDatas()) {
                for (ExcelQuery query : queries) {
                    if (!queryDataMap.containsKey(query.getQueryId())) {
                        continue;
                    }
                    // key: 匹配值，value: 匹配下 结果集合
                    Map<String, List<JSONObject>> listMap = queryDataMap.get(query.getQueryId());
                    // 算出主数据匹配值
                    String format = query.toFormat(query.getMatchColumn());
                    String keyVal = LabJsonUtil.formatCode(format, data);
                    if (StrUtil.isBlankIfStr(keyVal) || !listMap.containsKey(keyVal)) {
                        continue;
                    }
                    /*
                     * 为主数据赋值
                     * 单条数据时: 根据映射列进行赋值,再 根据加入属性设置
                     * 多条数据时，根据加入属性设置
                     */
                    List<JSONObject> list = listMap.get(keyVal);
                    if (list.size() == 1) {
                        List<String> mappers = query.toList(query.getMapperColumn());
                        if (!CollUtil.isEmpty(mappers)) {
                            JSONObject env = list.get(0);
                            for (String s : mappers) {
                                data.set(s,env.get(s));
                            }
                        }
                    }
                    if (!StrUtil.isBlankIfStr(query.getJoinProp())) {
                        data.set(query.getJoinProp(),list);
                    }
                }
            }
        }
    }

    private static final Pattern REGEX = Pattern.compile("\\$\\{(.*?)}");

    public static List<String> parseProp(String template) throws LabException {
        List<String> props = new ArrayList<>();
        if (StrUtil.isBlankIfStr(template)) {
            return props;
        } else {
            Matcher matcher = REGEX.matcher(template);

            while(matcher.find()) {
                String key = matcher.group(0);
                String prop = StrUtil.sub(key, 2, key.length() - 1);
                props.add(prop);
            }
            return props;
        }
    }

    public static String formatSql(String template, JSONObject data) throws LabException {
        try {
            if (StrUtil.isBlankIfStr(template)) {
                return "";
            } else {
                List<String> props = parseProp(template);
                if (!CollUtil.isEmpty(props)) {
                    JSONObject env = new JSONObject(data);
                    env.set("_args", data);
                    for (String prop : props) {
                        Object value = ScriptHelper.expression(prop, env, null);
                        String key = String.format("${%s}", prop);
                        if (value instanceof JSON) {
                            template = template.replace(String.format("\"%s\"", key), JSONUtil.toJsonPrettyStr(value));
                        } else {
                            template = template.replace(key, Convert.toStr(value, ""));
                        }
                    }
                }
                return StrUtil.format(template,data);
            }
        } catch (Exception e) {
            throw new LabException(String.format("格式化：%s 异常", template),e);
        }
    }

    /**
     * 执行sql查询
     * @param limsDb        数据库
     * @param querySql      查询sql
     * @param args          参数
     * @return
     */
    private List<JSONObject> queryDataSource(Db limsDb,String querySql,JSONObject args) {
        List<JSONObject> list = new ArrayList<>();
        try {
//            String sql = formatSql(querySql,args);
            List<Entity> entities = limsDb.query(querySql, args);
            if (!CollUtil.isEmpty(entities)) {
                for (Entity entity : entities) {
                    JSONObject object = new JSONObject();
                    Set<String> names = entity.getFieldNames();
                    for (String name : names) {
                        object.set(name,entity.get(name));
                    }
                    list.add(object);
                }
            }
        }catch (Exception e){
            throw new LabException("SQL统计异常",e);
        }
        return list;
    }

    /**
     * 加载关联map
     * @param excelOut  输出
     * @return  key: 工作页名称，value: 关联对象
     */
    private Map<String,ExcelJoin> loadJoinMap(ExcelOut excelOut) {
        LabQuery labQuery = new LabQuery().setPageSize(-1);
        labQuery.eq("outId",excelOut.getOutId());
        List<ExcelJoin> joins = excelJoinService.query(labQuery);
        Map<String,ExcelJoin> joinMap = new HashMap<>();
        if (!CollUtil.isEmpty(joins)) {
            labQuery = new LabQuery().setPageSize(-1);
            labQuery.eq("excelId",excelOut.getExcelId());
            List<ExcelSheet> sheets = excelSheetService.query(labQuery);
            Map<String,ExcelSheet> sheetMap = new HashMap<>();
            for (ExcelSheet sheet : sheets) {
                sheet.setColumns(CollUtil.newArrayList());
                sheetMap.put(sheet.getSheetName(),sheet);
            }

            labQuery = new LabQuery().setPageSize(-1).setSorts(CollUtil.newArrayList(new QuerySort().setField("columnOrder").setAsc(false)));
            for (ExcelJoin join : joins) {
                join.setColumns(CollUtil.newArrayList()).setSheet(sheetMap.get(join.getSheetName()));
                joinMap.put(join.getSheetName(),join);
            }
            labQuery.eq("excelId",excelOut.getExcelId()).in("sheetName",joinMap.keySet());
            List<ExcelColumn> columns = excelColumnService.query(labQuery);
            for (ExcelColumn column : columns) {
                if (joinMap.containsKey(column.getSheetName())){
                    joinMap.get(column.getSheetName()).getColumns().add(column);
                }
            }
        }
        return joinMap;
    }

    @SneakyThrows
    private QueryData queryData(ExcelOut excelOut,JSONObject args){
        ExcelTemplate excelTemplate = getById(excelOut.getExcelId());
        if (excelTemplate == null) {
            throw new LabException("模板不存在");
        }
        QueryData queryData = queryLims(excelTemplate,excelOut, args);
        if (CollUtil.isEmpty(queryData.getDatas())) {
            return queryData;
        }
        TimeInterval timer = DateUtil.timer();
        // 计算
        if (args.getBool("compute",false)) {
            log.info("---> {} 读取excel模板数据",excelOut.getOutName());
            // 加载关联配置 key: sheetName
            Map<String, ExcelJoin> joinMap = loadJoinMap(excelOut);
            // 从excel中读取关联数据 key: 关联属性，key: 关联值，value: 分组值
            Map<String, Map<String, List<JSONObject>>> joinData = BlExcelReadUtil.readJoin(excelTemplate, joinMap);
            log.info("---> {} 查询计算公式",excelOut.getOutName());
            // 查询计算公式
            LabQuery labQuery = new LabQuery().setPageSize(-1).setSorts(CollUtil.newArrayList(new QuerySort().setAsc(false).setField("computeOrder")));
            labQuery.eq("outId",excelOut.getOutId());
            List<ExcelCompute> computes = excelComputeService.query(labQuery);
            if (!CollUtil.isEmpty(computes)) {
                computes.sort(Comparator.comparing(ExcelCompute::getComputeOrder));
                for (ExcelCompute compute : computes) {
                    if (!StrUtil.isBlankIfStr(compute.getComputeScript())) {
                        compute.setScriptMd5(DigestUtil.md5Hex(compute.getComputeScript()));
                    }
                }
            } else if (CollUtil.isEmpty(joinData)) {
                return queryData;
            }
            log.info("---> {} 进入计算环节...",excelOut.getOutName());
            LabThreadPool threadPool = excelTemplate.threadPool();
            Semaphore semaphore = new Semaphore(threadPool.getMaxPoolSize(),true);
            CountDownLatch downLatch = new CountDownLatch(queryData.getDatas().size());
            for (JSONObject env : queryData.getDatas()) {
                semaphore.acquire();
                LabThreadPoolHelper.addTask(threadPool, new Runnable() {
                    @Override
                    public void run() {
                        try {
                            if (!CollUtil.isEmpty(joinData)) {
                                joinMapper(env,joinData,joinMap);
                            }
                            if (!CollUtil.isEmpty(computes)) {
                                dataCompute(env,computes,excelOut);
                            }
                            semaphore.release();
                        } catch (Exception e) {
                            log.error("数据计算异常:{}",env,e);
                            semaphore.release();
                        }finally {
                            downLatch.countDown();
                        }
                    }
                });
            }
            boolean await = downLatch.await(30, TimeUnit.SECONDS);
//            LabThreadPoolHelper.printState(threadPool);
//            log.info("---> {} 完成计算",excelOut.getOutName());
            log.info("-----> {} 计算耗时:{}",excelOut.getOutName(),timer.intervalRestart());
        }
        return queryData;
    }

    /**
     * 通过关配置，映射字段和添加参数
     * @param data      数据
     * @param joinData  关联数据
     * @param joinMap   关联配置
     */
    private void joinMapper(JSONObject data,Map<String, Map<String, List<JSONObject>>> joinData,Map<String, ExcelJoin> joinMap){
        for (Map.Entry<String, ExcelJoin> joinEntry : joinMap.entrySet()) {
            ExcelJoin excelJoin = joinEntry.getValue();
            Map<String, List<JSONObject>> listMap = joinData.get(excelJoin.getJoinProp());
            String format = excelJoin.toFormat(excelJoin.getMatchColumn());
            String keyVal = LabJsonUtil.formatCode(format,data);
            if (listMap.containsKey(keyVal)) {
                List<JSONObject> matchDatas = listMap.get(keyVal);
                if (matchDatas.size() == 1) {
                    List<String> list = excelJoin.toList(excelJoin.getMapperColumn());
                    if (!CollUtil.isEmpty(list)) {
                        JSONObject env = matchDatas.get(0);
                        for (String s : list) {
                            data.set(s,env.get(s));
                        }
                    }
                }
                if (!StrUtil.isBlankIfStr(excelJoin.getJoinProp())) {
                    data.set(excelJoin.getJoinProp(),matchDatas);
                }
            }
        }
    }

    /**
     * 数据公式计算
     * @param env           数据
     * @param computes      计算公式
     * @param excelOut      输出
     */
    private void dataCompute(JSONObject env,List<ExcelCompute> computes,ExcelOut excelOut) {
        try {
            env.set("excelId",excelOut.getExcelId());
            if (!env.containsKey(BlExcelReadUtil.CELL_STYPE)) {
                env.set(BlExcelReadUtil.CELL_STYPE,new JSONObject());
            }
            for (ExcelCompute compute : computes) {
                if (!StrUtil.isBlankIfStr(compute.getComputeQuery())) {
                    // 查询
                    GlobalDbConfig.setCaseInsensitive(false);
                    Db limsDb = Db.use(labDynamicSourceService.getDataSource());
                    String querySql = LabJsonUtil.formatCode(compute.getComputeQuery(),env);
                    List<Entity> entities = limsDb.query(querySql, env);
                    if (!CollUtil.isEmpty(entities) && entities.size() == 1) {
                        for (Entity entity : entities) {
                            JSONObject object = new JSONObject();
                            Set<String> names = entity.getFieldNames();
                            for (String name : names) {
                                object.set(name,entity.get(name));
                            }
                            if (StrUtil.isBlankIfStr(compute.getComputeProp())) {
                                env.putAll(object);
                            } else {
                                env.set(compute.getComputeProp(),object);
                            }
                        }
                    }
                }
                if (!StrUtil.isBlankIfStr(compute.getComputeScript())) {
                    JSONObject data = new JSONObject(env);
                    data.set("_env",env);
                    if (!data.containsKey(BlExcelReadUtil.CELL_STYPE)) {
                        data.set(BlExcelReadUtil.CELL_STYPE,env.getJSONObject(BlExcelReadUtil.CELL_STYPE));
                    }
                    // 计算
                    Object expression = ScriptHelper.expressionIgnore(compute.getComputeScript(), data, compute.getScriptMd5());
                    if (expression instanceof Map) {
                        JSONObject object = JSONUtil.parseObj(expression);
                        if (StrUtil.isBlankIfStr(compute.getComputeProp())) {
                            env.putAll(object);
                        } else {
                            env.set(compute.getComputeProp(),object);
                        }
                    }
                    env.set(BlExcelReadUtil.CELL_STYPE,data.getJSONObject(BlExcelReadUtil.CELL_STYPE));
                }
            }
        }catch (Exception e){
            log.error("数据计算失败:{}",env,e);
        }
    }

    @Override
    public String exportExcel(String outId, JSONObject args) {
        args.set("compute",true);
        args.set("report",true);
        ExcelOut excelOut = excelOutService.getById(outId);
        if (excelOut == null) {
            throw new LabException("输出不存在");
        }
        ExcelTemplate excelTemplate = getById(excelOut.getExcelId());
        if (excelTemplate == null) {
            throw new LabException("模板不存在");
        }
        QueryData queryData = queryData(excelOut, args);
        if (CollUtil.isEmpty(queryData.getDatas())) {
            return StrUtil.EMPTY;
        }
        if (!FileUtil.isFile(excelTemplate.getExcelPath())) {
            throw new LabException("模板文件丢失");
        }
        TimeInterval timer = DateUtil.timer();
        Map<String,ExcelSheet> sheetMap = new HashMap<>();
        LabQuery sheetQuery = new LabQuery().setPageSize(-1);
        sheetQuery.eq("excelId",excelTemplate.getExcelId());
        List<ExcelSheet> sheets = excelSheetService.query(sheetQuery);
        if (!CollUtil.isEmpty(sheets)) {
            for (ExcelSheet sheet : sheets) {
                if (StrUtil.equals("异常数据库",sheet.getSheetName()) || StrUtil.equals("化学指标",sheet.getSheetName())) {
                    sheet.setColumnRow(sheet.getColumnRow() +  1);
                }
                sheetMap.put(sheet.getSheetName(),sheet);
            }
        }

        String suffix = FileUtil.getSuffix(excelTemplate.getExcelPath());
        String parent = FileUtil.getParent(excelTemplate.getExcelPath(), 1);
        String tmp = String.format("%s%s%s",parent, SystemUtil.getOsInfo().getFileSeparator(), DateUtil.today());
        if (!FileUtil.isDirectory(tmp)) {
            FileUtil.mkdir(tmp);
        }
        String newExcel = String.format("%s%s%s-%s.%s",tmp,SystemUtil.getOsInfo().getFileSeparator(),excelOut.getOutName(),
                DatePattern.PURE_DATETIME_FORMAT.format(new Date()),suffix);
        File file = FileUtil.copy(excelTemplate.getExcelPath(), newExcel, true);
        if (sheetMap.containsKey(excelOut.getOutName())) {
            queryData.setStartIndex(sheetMap.get(excelOut.getOutName()).getColumnRow());
        }
        BlExcelReadUtil.write(queryData,excelOut.getOutName(),file.getAbsolutePath());
        log.info("-----> {} excel写入耗时:{}",excelOut.getOutName(),timer.intervalRestart());
        if (args.getBool("report")) {
            syncCreateDocx(excelTemplate,excelOut,sheetMap,queryData,tmp,args,file);
//            createDocx(excelOut,sheetMap,queryData,tmp,args,file);
        }
        byte[] bytes = FileUtil.readBytes(file);
        String encode = Base64Encoder.encode(bytes);
        FileUtil.del(file);
        return encode;
    }


    private void syncCreateDocx(ExcelTemplate excelTemplate,ExcelOut excelOut,Map<String,ExcelSheet> sheetMap, QueryData queryData,String tmp,JSONObject args,File file){
        LabQuery labQuery = new LabQuery().setPageSize(-1);
        labQuery.eq("outId",excelOut.getOutId());
        List<ExcelDocx> docxes = excelDocxService.query(labQuery);
        if (CollUtil.isEmpty(docxes)) {
            return;
        }
        TimeInterval timer = DateUtil.timer();
        try {
            LabThreadPool threadPool = excelTemplate.threadPool();
            Semaphore semaphore = new Semaphore(NumberUtil.min(threadPool.getMaxPoolSize(),docxes.size()),true);
            CountDownLatch downLatch = new CountDownLatch(docxes.size());
            ConcurrentHashMap<String,QueryData> dataMap = new ConcurrentHashMap<>();
            for (ExcelDocx docx : docxes) {
                semaphore.acquire();
                LabThreadPoolHelper.addTask(threadPool, () -> {
                    try {
                        tryWriteDocx(dataMap,docx,excelOut,sheetMap,queryData,tmp,args,file);
                    }catch (Exception e){
                        log.error("---> {} 生成报告异常 {}",excelOut.getOutName(),docx.getTargetSheet());
                    } finally {
                        downLatch.countDown();
                        semaphore.release();
                    }
                });
            }
            downLatch.await();
            if (!CollUtil.isEmpty(dataMap)) {
                for (Map.Entry<String, QueryData> entry : dataMap.entrySet()) {
                    QueryData entryValue = entry.getValue();
                    if (CollUtil.isEmpty(entryValue.getDatas())) {
                        continue;
                    }
                    writeDocx(excelOut,entry.getValue(),sheetMap,tmp,file);
                }
            }
        }catch (Exception e){
            throw new LabException("报告生成异常",e);
        }
        log.info("-----> {} 报告生成耗时:{}",excelOut.getOutName(),timer.intervalRestart());
    }

    private QueryData tryWriteDocx(ConcurrentHashMap<String,QueryData> dataMap,ExcelDocx docx, ExcelOut excelOut,Map<String,ExcelSheet> sheetMap, QueryData queryData,String tmp,JSONObject args,File file){
        QueryData data = createDocx(docx, excelOut, queryData, args);
        try {
            dataMap.put(docx.getDocxId(),data);
//            writeDocx(excelOut,data,sheetMap,tmp,file);
        }catch (Exception e){
//            dataMap.put(docx.getDocxId(),data);
        }
        return data;
    }

    private QueryData createDocx(ExcelDocx docx,ExcelOut excelOut,QueryData queryData,JSONObject args){
        log.info("---> {} 开始生成报告 {}",excelOut.getOutName(),docx.getTargetSheet());
        QueryData docData = new QueryData().setDatas(queryData.getDatas()).setExcelDocx(docx);
        LabQuery docQuery = new LabQuery().setPageSize(-1).setSorts(CollUtil.newArrayList(new QuerySort().setField("columnOrder").setAsc(true)));
        docQuery.eq("excelId",excelOut.getExcelId()).eq("sheetName",docx.getTargetSheet());
        docData.setColumns(excelColumnService.query(docQuery));
        JSONObject env = JSONUtil.parseObj(docData);
        // 关联工作页
        List<String> joinSheets = JSONUtil.toList(docx.getJoinSheet(), String.class);
        if (!CollUtil.isEmpty(joinSheets)) {
            for (String joinSheet : joinSheets) {
                LabQuery joinQuery = new LabQuery().setPageSize(1);
                joinQuery.eq("excelId",excelOut.getExcelId()).eq("outName",joinSheet);
                List<ExcelOut> joinOuts = excelOutService.query(joinQuery);
                if (CollUtil.isEmpty(joinOuts)) {
                    continue;
                }
                log.info("---> {} 关联工作页 {} - {}",excelOut.getOutName(),docx.getTargetSheet(),joinSheet);
                QueryData joinData = queryData(joinOuts.get(0), args);
                env.set(joinSheet,joinData.getDatas());
            }
        }
        //  输出数据源处理
        if (!StrUtil.isBlankIfStr(docx.getSourceScript())) {
            Object o = ScriptHelper.expressionIgnore(docx.getSourceScript(), env, DigestUtil.md5Hex(docx.getSourceScript()));
            if (o instanceof Collection) {
                List<JSONObject> list = (List<JSONObject>) o;
                docData.setDatas(list);
                env.set("datas",list);
            }
        }
        docData.setEnv(env);
        return docData;
    }

    private void writeDocx(ExcelOut excelOut,QueryData docData,Map<String,ExcelSheet> sheetMap,String tmp,File file){
        ExcelDocx docx = docData.getExcelDocx();
        if (!StrUtil.isBlankIfStr(docx.getDocxPath())) {
            log.info("---> {} 生成docx报告 {}-{}",excelOut.getOutName(),docx.getTargetSheet(),docx.getDocxName());
            ReportTemplate template = new ReportTemplate();
            template.setTemplateName(docx.getDocxName()).setSourceScript(docx.getSourceScript()).setBytes(FileUtil.readBytes(docx.getDocxPath()));
            ReportResponse response = ReportHelper.createReport(template, docData.getEnv());
            String mainName = FileUtil.mainName(docx.getDocxName());
            String newDocx = String.format("%s%s%s-%s.%s",tmp,SystemUtil.getOsInfo().getFileSeparator(),mainName,
                    DatePattern.PURE_DATETIME_FORMAT.format(new Date()),response.getSuffix());
            FileUtil.writeBytes(response.getBytes(),newDocx);
        } else if (!StrUtil.isBlankIfStr(docx.getTargetSheet())) {
            if (sheetMap.containsKey(docx.getTargetSheet())) {
                docData.setStartIndex(sheetMap.get(docx.getTargetSheet()).getColumnRow());
            }
            log.info("---> {} 生成excel报告 {} ==> {}",excelOut.getOutName(),docx.getTargetSheet(),docData.getDatas().size());
            BlExcelReadUtil.write(docData,docx.getTargetSheet(),file.getAbsolutePath());
        }
    }

    private QueryData createDocx(ExcelDocx docx, ExcelOut excelOut,Map<String,ExcelSheet> sheetMap, QueryData queryData,String tmp,JSONObject args,File file){
        QueryData docData = createDocx(docx, excelOut, queryData, args);
        try {
            writeDocx(excelOut,docData,sheetMap,tmp,file);
        }catch (Exception e){
            log.error("excel写入{}失败",docx.getTargetSheet(),e);
        }
        return docData;
    }

    private void createDocx(ExcelOut excelOut,Map<String,ExcelSheet> sheetMap, QueryData queryData,String tmp,JSONObject args,File file){
        LabQuery labQuery = new LabQuery().setPageSize(-1);
        labQuery.eq("outId",excelOut.getOutId());
        List<ExcelDocx> docxes = excelDocxService.query(labQuery);
        if (CollUtil.isEmpty(docxes)) {
            return;
        }
        for (ExcelDocx docx : docxes) {
            createDocx(docx,excelOut,sheetMap,queryData,tmp,args,file);
        }
    }
}
